package sk.stuba.fiit.foo07.genex.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import sk.stuba.fiit.foo07.genex.beans.QuestionType;

/**
 * @author palo
 * 
 * 
 * 
 */

public class QuestionTypeDaoDerby extends BasicDao implements QuestionTypeDao {

    private static final String sqlSelectAll = "SELECT Name, Description, QuestionTypeID FROM QuestionType";
    private static final String sqlSelectQuestionByID = "SELECT Name,Description,QuestionType.questionTypeID"
            + " FROM Question, QuestionType WHERE Question.questionID = ? AND  QuestionType.questionTypeID=Question.questionTypeID";
    private static final String sqlSelectQuestionTypeByName = "SELECT QuestionType.questionTypeID FROM QuestionType WHERE name = ?";

    private QuestionType qt;
    private PreparedStatement st;
    private ResultSet rs;

    public QuestionTypeDaoDerby(Connection con) {
        super(con);
    }

    @Override
    public QuestionType getQuestionTypeByQuestionID(Integer questionID)
            throws SQLException {
        try {
            qt = new QuestionType();
            st = con.prepareStatement(sqlSelectQuestionByID);
            st.setInt(1, questionID);
            rs = st.executeQuery();

            rs.next();
            qt.setName(rs.getString("Name"));
            qt.setDescription(rs.getString("Description"));
            qt.setQuestionTypeID(rs.getInt("questionTypeID"));

            rs.close();

        } catch (SQLException e) {
            qt = null;
            st = null;
            rs = null;
            throw e;
        }
        return qt;
    }

    @Override
    public ArrayList<QuestionType> getAllQuestionTypes() throws SQLException {
        ArrayList<QuestionType> types = null;
        try {
            st = con.prepareStatement(sqlSelectAll,
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
            rs = st.executeQuery();

            rs.last();
            types = new ArrayList<QuestionType>(rs.getRow());
            rs.beforeFirst();

            while (rs.next()) {
                types
                        .add(new QuestionType(rs.getString("Name"), rs
                                .getString("Description"), rs
                                .getInt("questionTypeID")));
            }
            rs.close();

        } catch (SQLException e) {
            qt = null;
            st = null;
            rs = null;
            throw e;
        }
        return types;
    }

    @Override
    public Integer getQuestionTypeByName(String qTypeName) throws SQLException {
        Integer qTypeID = new Integer(0);
        try {
            st = con.prepareStatement(sqlSelectQuestionTypeByName);
            st.setString(1, qTypeName);
            rs = st.executeQuery();

            rs.next();
            qTypeID = rs.getInt("questionTypeID");
        } catch (SQLException sqle) {
            st = null;
            rs = null;
            throw sqle;
        }
        return qTypeID;
    }

}
